

#include "SQLGateway.hpp"
#include "FieldNameLookup.hpp"


SQLGateway::SQLGateway() : trans(NULL)
{
  
}


bool SQLGateway::Connect()
{
  try {
    if (!this->conn.connect("yahfin", "localhost", "root", "Pass72"))
    {
      return false; 
    }
    this->trans = new mysqlpp::Transaction(this->conn, mysqlpp::Transaction::serializable, mysqlpp::Transaction::session);
  } catch (mysqlpp::Exception& er) {
    this->strLastSQLError = er.what();   // remember if we don't throw we can just use conn.error()
    return false;
  }
  
  return true;
}


bool SQLGateway::transactionCommit()
{
  if ( !(this->trans) ) { return false; }
  
  try {
    trans->commit();  
  } catch (mysqlpp::BadQuery er) { 
    this->strLastSQLError = er.what();
    return false;
  } catch (mysqlpp::BadConversion &er) {
    this->strLastSQLError = er.what();  
    return false;
  } catch (mysqlpp::Exception& er) {
    this->strLastSQLError = er.what();
    return false;
  }
  
  return true;
}


bool SQLGateway::transactionRollback()
{
  if ( !(this->trans) ) { return false; }
  
  try {
    trans->rollback();
  } catch (mysqlpp::BadQuery er) { 
    this->strLastSQLError = er.what();
    return false;
  } catch (mysqlpp::BadConversion &er) {
    this->strLastSQLError = er.what();  
    return false;
  } catch (mysqlpp::Exception& er) {
    this->strLastSQLError = er.what();
    return false;
  }
  
  return true;
}


// Given tableName, fill setFieldNames with all fields in the table.
// If return false, some error occurred and setfieldNames is invalid.

bool SQLGateway::getSQLTableFieldNames(std::string tableName, SetString& setFieldNames)
{
  this->clearPreviousError();   
  setFieldNames.clear();
  
  try 
  {
    mysqlpp::Query query = this->conn.query();
    query << "DESCRIBE " << mysqlpp::escape << tableName << ";";
    this->strLastSQLQuery = query.str();
    mysqlpp::StoreQueryResult result = query.store();
    
    for (std::size_t i = 0; i < result.num_rows(); ++i)
    {
      // Remember result[][] is a mysqlpp::String and must be converted to std::string
      std::string fieldName = std::string(result[i]["Field"]);
      setFieldNames.insert(fieldName);
    }
  } catch (mysqlpp::BadQuery er) {              
    this->strLastSQLError = er.what();          
    return false;
  } catch (mysqlpp::BadConversion &er) {
    this->strLastSQLError = er.what();  
    return false;
  } catch (mysqlpp::Exception& er) {
    this->strLastSQLError = er.what();
    return false;
  }
  
  return true;  
}



/* ************************************************************************ */
// For working with ResultQuote 
/* ************************************************************************ */


// In the VALUES ( ) block of our inserts or updates, given a FieldNameType, should the value be quoted?
// Default to true for everything, and turn it off for known types that do not need quotes
bool SQLGateway::shouldTypesValueBeQuoted(FieldNameType fieldNameType)
{
  if (fieldNameType == FieldNameType::Int64 || fieldNameType == FieldNameType::Double)
  {
    return false;  
  }
  return true;
}



// Scans through the FieldName's in ResultQuote, examines the schema of tableName, and returns an std::set of any
// fields from ResultQuote that do not have a column in the table.
// This is used to avoid inserting fields from a ResultQuote that don't exist in the SQL table.
bool SQLGateway::getFieldsNotInTable(std::string tableName, ResultQuote& resultQuote, SetString& setFieldsNotInTable)
{
  this->clearPreviousError();
  setFieldsNotInTable.clear();
  
  SetString setSQLFieldNames;
  
  // Get a list of all FieldNames in the SQL table
  if (!getSQLTableFieldNames(tableName, setSQLFieldNames)) {
    // Above function will already set error values if it fails, return that.
    return false;
  }
  
  // Get a list of all FieldNames in the resultQuote
  VectorFieldName vecRQFieldNames = resultQuote.getAllFieldNames();
  
  // Iterate through our ResultQuote field name vector
  for (VectorFieldName::const_iterator itrFieldNames = vecRQFieldNames.begin(); itrFieldNames != vecRQFieldNames.end(); ++itrFieldNames)
  {
    // Convert the current FieldName to a string by extracting it from resultQuote.
    // The lookup failing is a major internal error somewhere in the class, where the vector and map don't agree.
    std::string strFieldName;
    if ( !(resultQuote.lookupFieldNameString(*itrFieldNames, strFieldName)) ) { return false; }
    
    // Does the current resultQuote FieldName exist in the SQL FieldName set?
    SetString::iterator itrFind = setSQLFieldNames.find(strFieldName);
    
    if (itrFind == setSQLFieldNames.end()) {
      // No it does not exist, add it to our NotInTable set.
      setFieldsNotInTable.insert(strFieldName);
    }
    // If it does exist, do nothing, go to the next name
  }
  return true;
}



// Insert a ResultQuote into tableName. Optionally return a list of fields that were in ResultQuote but not the table.
// These fields will be silently dropped.

// Some callers may not want setFieldsNotInTable. In order to avoid dealing with a pointer from the caller, these two
// overloaded functions accept either a reference or no reference, and handle the pointer internally

////////////////////////////////////////
// Public overload 
bool SQLGateway::insertResultQuote(std::string tableName, ResultQuote& resultQuote, SetString& setReturnFieldsNotInTable)
{
  return (this->insertResultQuote(tableName, resultQuote, &setReturnFieldsNotInTable));
}

////////////////////////////////////////
// Public overload 
bool SQLGateway::insertResultQuote(std::string tableName, ResultQuote& resultQuote)
{
  return (this->insertResultQuote(tableName, resultQuote, NULL));
}


///////////////////////////////////////////////
// Private function
bool SQLGateway::insertResultQuote(std::string tableName, ResultQuote& resultQuote, SetString* setReturnFieldsNotInTable)
{
  
  SetString setMissingFromSQL;
  if (! (this->getFieldsNotInTable(tableName, resultQuote, setMissingFromSQL)) ) {
    // Failed, previous function set SQL errors, just return false.
    return false;  
  }
  
  // Get a list of all field names in resultQuote
  VectorFieldName vecFieldName = resultQuote.getAllFieldNames();
  
  // Create a list of FieldNames for the insert
  VectorString vecNames, vecValues;
  VectorFieldNameType vecTypes;
  
  for (VectorFieldName::const_iterator itrLoop = vecFieldName.begin(); itrLoop != vecFieldName.end(); ++itrLoop)
  {
    // Lookup the string field name
    std::string strFieldName;
    if ( !(resultQuote.lookupFieldNameString(*itrLoop, strFieldName)) ) { return false; }      
    
    // Is the FieldName in our missing set?
    SetString::const_iterator itrFind = setMissingFromSQL.find(strFieldName);
    
    if (itrFind == setMissingFromSQL.end()) {
      // Not in the missing set, this is a good value
      vecNames.push_back(strFieldName);
      // Get the trimmed data string
      std::string strDataValue;
      if ( !(resultQuote.lookupDataString(*itrLoop, strDataValue, TrimOptions::ForSQL, true)) ) { return false; }      	
      vecValues.push_back(strDataValue);
      FieldNameType fieldNameType;
      if ( !(resultQuote.lookupFieldType(*itrLoop, fieldNameType)) ) { return false; }      	
      vecTypes.push_back(fieldNameType);
    }
  }
  
 
  // Now insert the fields and data  
  // INSERT INTO tableName (...) values (...)
  try {
    
    
    mysqlpp::Query query = this->conn.query();
    query << "REPLACE INTO " << mysqlpp::escape << tableName << "(";
    for (unsigned int idx = 0; idx < vecNames.size(); ++idx)
    {
      if (idx > 0) { query << ", "; }
      query << "`" << mysqlpp::escape << vecNames[idx] << "`";
    }
    query << ") VALUES (";
    for (unsigned int idx = 0; idx < vecValues.size(); ++idx)
    {
      if (idx > 0) { query << ", "; }
      if (vecValues[idx].empty()) {    // Empty string indicates no value
	query << mysqlpp::escape << "NULL";       // Store as a mysql null
      } else {
	if (this->shouldTypesValueBeQuoted(vecTypes[idx])) {
	  query << "'" << mysqlpp::escape << vecValues[idx] << "'";
	} else {
	  query << mysqlpp::escape << vecValues[idx];
	}
      }
    }
    
    query << ");";
    
    this->strLastSQLQuery = query.str();
    query.execute();
    
    
  } catch (mysqlpp::BadQuery er) {  
    /* DEBUG, dump names and values
    FieldNameLookup look;
    for (unsigned int idx = 0; idx < vecValues.size(); ++idx)
    {
      std::cout << "Name [" << vecNames[idx] << "]     Type: " << look.FieldNameTypeToString(vecTypes[idx]) << "\n";
      std::cout << "Value [" << vecValues[idx] << "]\n";
      
    }
    */
    this->strLastSQLError = er.what();
    return false;
  } catch (mysqlpp::BadConversion &er) {
    this->strLastSQLError = er.what();  
    return false;
  } catch (mysqlpp::Exception& er) {
    this->strLastSQLError = er.what();
    return false;
  }
  
  
  return true;
}

